#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Date    : 2022
# @Author  : lingfeng.hu@nokia-sbell.com)
# @Link    : TBD
# @Version : $Id$

# https://jiradc.ext.net.nokia.com/rest/api/2/issue/FPB-662472
# from CollectL3HzFBData import getTeam
import os
from jira import JIRA
from openpyxl import Workbook
from time import strftime, localtime
from datetime import datetime
from threading import Thread

# from FeatureBuildsSchedule import buildFeatureBuildsTimeline, getCurrentFB

teamMapping = {
    "1174" : "L3_4G FT ET_HZ64_SG4",
    "5253" : "L3_5GL3ET_HZH_Jazz",
    "5254" : "L3_5GL3ET_HZH_Blues",
    "5255" : "L3_5GL3ET_HZH_Squad C",
    "5351" : "L3_5GL3ET_HZH_Rock",
    "6261" : "L3_5GL3ET_HZH_SHIELD",
    "5235" : "L3_5GL3_SG09_SHA_Groovyard",
    "8364" : "L3_5G L3 ET_HZ64_SG4",		
    "5236" : "L3_5GL3_SG09_SHA_Typhoon",
    "5237" : "L3_5GL3_SG09_SHA_Weeasy",
    "6624" : "L3_5G L3_HZ64_FTH1",
    "6625" : "L3_5G ET HZ64_FTH_5G",
    "6626" : "L3_5G L3 EFS_HZ64_FTH1",
    "8342" : "L3_5GL3ET_NJG_Beams",
    "8050" : "L3_5GL3_SG08_HZH_PER",
    "5233" : "L3_5GL3_SG08_HZH_BARRETT",
    "6262" : "L3_5GL3_SG14_HZH_DRAGONBALL",
    "8536" : "L3_5GL3_SG08_HZH_Center",
    "5234" : "L3_5GL3_SG08_HZH_BEE",
    "5160" : "L3_ASIRAP_NJG_SW1_L3",
    "5219" : "L3_5GL3_SG01_HZH_ACE",			
    "5220" : "L3_5GL3_SG01_HZH_Carry",
    "5250" : "L3_5GL3_SG05_HZH_Blink",			
    "6389" : "L3_5GL3EFS_HZH_CPEFS",
    "6391" : "L3_5GL3_SG05_HZH_Easy",
    "8514" : "L3_5GL3_SG05_HZH_Frame",
    "5243" : "L3_5GL3_SG12_HZH_GELIVABLE",
    "5244" : "L3_5GL3_SG12_HZH_Superman",
    "4780" : "L3_FZRTSP_NJG_Glimmer_L3",
    "5160" : "L3_ASIRAP_NJG_SW1",
    "8043" : "L3_5G L3 ET_HZ64_Woodpecker",
    "8499" : "L3_5G L3 EFS_HZ64_FTH11",
    "7955" : "L3_5GL3_NJG_Mars",
    "7962" : "L3_5GL3_NJG_Nemo",
    "8469" : "L3_5GL3_NJG_Mercury",
    "8619" : "L3_5G L3_HZ64_FTH11",
    "8042" : "L3_5G L3_HZ64_FTH25",
    "8470" : "L3_5G L3_HZ64_TrunkForce",
    "8376" : "L3_5G L3_HZ64_FTH99",
    "8044" : "L3_5G L3 EFS_HZ64_NorthStar",
    "8041" : "L3_5G L3_HZ64_FTH24",
    "8482" : "L3_5GL3_NJG_Jupiter",
    "8182" : "DCM_5G_L3_EFS_BJ_JobRotation",
    "8183" : "DCM_5G_L3_BJ_JobRotation",
    "5241" : "L3_5GL3_SG11_SHA_ROAD",
    "8038" : "L3_5G L3_HZ64_FTH5",
    "8040" : "L3_5G L3_HZ64_FTH22",
    "5228" : "L3_5G L3_HZ64_STAR",
    "5242" : "L3_5GL3_SG11_SHA_TAO",
    "5231" : "L3_5G L3_HZ64_GAEA",
    "5252" : "L3_5GL3_SG11_SHA_Giraffe",
    "8413" : "L3_5G L3_HZ64_BalanceGold",
    "5222" : "L3_5GL3_SG03_HZH_Eagle",
    "5224" : "L3_5GL3_SG03_HZH_Trident",
    "5225" : "L3_5GL3_SG03_HZH_Venus",
    "5226" : "L3_5GL3_SG04_HZH_Dragonhawk",
    "8039" : "L3_5G L3_HZ64_FTH21",
    "5227" : "L3_5GL3_SG04_HZH_Griffin",
    "6522" : "L3_5GL3_SG02_HZH_KongFu",
    "6801" : "L3_5GL3_SG02_HZH_MAMBA",
    "8463" : "L3_5G L3 ET_HZ64_SG2",
    "5223" : "L3_5G L3_HZ64_Ocean",
    "5229" : "L3_5G L3_HZ64_BINGO",	
    "8580" : "L3_5GL3EFS_NJG_Venus",
    "8596" : "DCM_5G_L3_ET_BJ_JobRotation",
    "6697" : "L2L3_ET_CN2",
    "1510" : "L3_DU_HZ",
    "2244" : "ECE_L3_HZ",
    "6874" : "L2L3_ET_CN3",
    "852"  : "L3_CU_HZ",
    "864"  : "L2L3_EFS_HZ",
    "868"  : "L2L3_EFS_Wro",
    "8731" : "L3_5G L3_HZ64_SCMCI",
    "8806" : "L3_5GL3_SG14_HZH_DRAGONFLY",
    "8735" : "L3_5GL3_NJG_Saturn"
}

def getItemId(issue):
    return issue.fields.customfield_38702 if issue.fields.customfield_38702 else ''

def getKey(issue):
    return issue.key if issue.key else ''

def getSummary(issue):
    return issue.fields.summary if issue.fields.summary else ''

def getPlannedSystemRelease(issue):
    return issue.fields.customfield_38724[0].value if issue.fields.customfield_38724 else ''

def getBusinessPrio1(issue):
    return issue.fields.customfield_38716 if issue.fields.customfield_38716 else ''

def getAssignee(issue):
    return issue.fields.assignee.name if issue.fields.assignee else ''

def getStatus(issue):
    return issue.fields.status.name if issue.fields.status else ''

def getTeam(issue):
    if issue.fields.customfield_29790:
        if issue.fields.customfield_29790 in teamMapping.keys():
            return teamMapping[issue.fields.customfield_29790]
        else:
            return issue.fields.customfield_29790
    else: 
        return ''

def getLabels(issue):
    return ','.join(issue.fields.labels) if issue.fields.labels else ''

def getTimeRemaining(issue):
    return issue.fields.customfield_43291 if issue.fields.customfield_43291 else 0

def getTimeLogged(issue):
    return issue.fields.customfield_43290 if issue.fields.customfield_43290 else 0

def getOriginalEstimate(issue):
    return issue.fields.customfield_43292 if issue.fields.customfield_43292 else 0

def getStartFb(issue):
    try:
        return int(issue.fields.customfield_38694) if issue.fields.customfield_38694 else ''
    except:
        return 0

def getEndFb(issue):
    try:
        return int(issue.fields.customfield_38693) if issue.fields.customfield_38693 else ''
    except:
        return 0

def getFbCommitStatus(issue):
    return issue.fields.customfield_38758.value if issue.fields.customfield_38758 else ''

def getStretchReason(issue):
    return issue.fields.customfield_38727 if issue.fields.customfield_38727 else ''

def getDelayReason(issue):
    return issue.fields.customfield_38759[0].value if issue.fields.customfield_38759 else ''

def getDelayExplanation(issue):
    return issue.fields.customfield_38755 if issue.fields.customfield_38755 else ''

def getRfcFB(issue):
    try:
        return int(issue.fields.customfield_43490.value) if issue.fields.customfield_43490 else ''
    except:
        return 0

def getRfcStatus(issue):
    return issue.fields.customfield_38728.value if issue.fields.customfield_38728 else ''

def getCompetenceArea(issue):
    return issue.fields.customfield_38690.value if issue.fields.customfield_38690 else ''

def getFs0Ee(issue):
    return issue.fields.customfield_38737 if issue.fields.customfield_38737 else ''

def getFs2Ee(issue):
    return issue.fields.customfield_38692 if issue.fields.customfield_38692 else ''

def getFS2Status(issue):
    return issue.fields.customfield_38705 if issue.fields.customfield_38705 else ''

def getFotLeader(issue):
    return issue.fields.customfield_38695.displayName if issue.fields.customfield_38695 else ''

def getWorkType(issue):
    return issue.fields.customfield_38368.value if issue.fields.customfield_38368 else ''

def getIssueType(issue):
    return issue.fields.issuetype.name if issue.fields.issuetype else ''

def getFeatureId(issue):
    return issue.fields.customfield_37381 if issue.fields.customfield_37381 else ''

def getReqArea(issue):
    return issue.fields.customfield_38712 if issue.fields.customfield_38712 else ''

def getActType(issue):
    return issue.fields.customfield_38750.value if issue.fields.customfield_38750 else ''

def getText2(issue):
    return issue.fields.customfield_38727 if issue.fields.customfield_38727 else ''

def getSpecEndFB(issue):
    return issue.fields.customfield_38739 if issue.fields.customfield_38739 else ''

def getCustomer(issue):
    customerList = []
    if issue.fields.customfield_32006:
        for customer in issue.fields.customfield_32006:
            customerList.append(customer.value)
        return ','.join(customerList)
    else:
        return ''

def getTargetReleases(issue):
    try:
        return issue.fields.customfield_38710 if issue.fields.customfield_38710 else ''
    except AttributeError:
        return ''

def getCreatedTime(issue):
    return issue.fields.created if issue.fields.created else ''

def getUpdatedTime(issue):
    return issue.fields.updated if issue.fields.updated else ''

def getResolvedTime(issue):
    return issue.fields.resolutiondate if issue.fields.resolutiondate else ''

# jirashell -s https://jiradc.ext.net.nokia.com -u <user> -p <passwd>
# issue=jira.issue('FPB-263466')
# issue.fields.customfield_38759[0].value


today = strftime("%Y-%m-%d", localtime())
startTime = datetime.now()
# today = '{:0>2s}'.format(str(now.year)) + '-' + '{:0>2s}'.format(str(now.month)) + '-' + '{:0>2s}'.format(str(now.day))
# print(today)

# featureBuilds = buildFeatureBuildsTimeline()
# currentFB = getCurrentFB(today, featureBuilds)
# print('now++ current FB is %s' %currentFB.getFB())

options = {
    'server': 'https://jiradc.ext.net.nokia.com'
    }

# xxx is windows account, yyy is password
userName = 'xxx'
passWord = 'yyy'
jira = JIRA(options, auth=(userName, passWord))

tmpFilter = jira.filter(344092)
print(tmpFilter)

nokiaJiraDcResultLimitNum='1000'
# nokiaJiraDcResultLimitNum='1'
jiraIssueKeys = []

backlogCAFilter = '344093'
backlogEpicFilter = '345672'
backlogFS2EeFilter = '362555'
backlogFFBFilter = '344092'
backlogSubtaskFilter = '362758'

def getIssues(filter, filterFields):
    now = datetime.now()
    filter = 'filter='+filter
    print("{} fetch {}-{}".format(filter, '0', nokiaJiraDcResultLimitNum))
    issuesJira = jira.search_issues(filter, startAt=0, maxResults=nokiaJiraDcResultLimitNum, fields=[x[1] for x in filterFields])
    print("{} spend time: {} in this round".format(filter, datetime.now() - now))
    print("{} total: {}".format(filter, issuesJira.total))

    totalIssuesNum = issuesJira.total
    if int(nokiaJiraDcResultLimitNum) < totalIssuesNum:
        continueRoundCount = totalIssuesNum//int(nokiaJiraDcResultLimitNum)
        for round in range(continueRoundCount):
            startIndex = int(nokiaJiraDcResultLimitNum) * (round+1)
            print("{} fetch next: {}-{}".format(filter, startIndex, startIndex+int(nokiaJiraDcResultLimitNum)-1))
            startTime = datetime.now()
            issuesJira.extend(jira.search_issues(filter, startAt=startIndex, maxResults=nokiaJiraDcResultLimitNum, fields=[x[1] for x in filterFields]))
            print("{} spend time: {} in this round".format(filter, datetime.now() - startTime))
    
    return issuesJira

workingDir = r'C:\Users\linhu\OneDrive - Nokia\Documents\workspace\pythonCoding\5g_cp_utils\Planning\FusionTools'
os.chdir(workingDir)
wb = Workbook()
ws_backlog_CA = wb.active
ws_backlog_CA.title = "Backlog_CA"
ws_backlog_Epic = wb.create_sheet(title="Backlog")
ws_backlog_FS2EE = wb.create_sheet(title="FS2EE")
ws_backlog_FFB = wb.create_sheet(title="FFB")
ws_backlog_Subtask = wb.create_sheet(title="Subtask")


def exportCAIssueToExcel(ws, issue, rowIndex, columnIndex):
        # print(issue.key)
        ws.cell(row=rowIndex, column=columnIndex).value = getItemId(issue)
        ws.cell(row=rowIndex, column=columnIndex+1).value = getKey(issue)
        ws.cell(row=rowIndex, column=columnIndex+2).value = getSummary(issue)
        ws.cell(row=rowIndex, column=columnIndex+3).value = getPlannedSystemRelease(issue)
        ws.cell(row=rowIndex, column=columnIndex+4).value = getBusinessPrio1(issue)
        ws.cell(row=rowIndex, column=columnIndex+5).value = getAssignee(issue)
        ws.cell(row=rowIndex, column=columnIndex+6).value = getStatus(issue)
        ws.cell(row=rowIndex, column=columnIndex+7).value = getTeam(issue)
        ws.cell(row=rowIndex, column=columnIndex+8).value = getLabels(issue)
        ws.cell(row=rowIndex, column=columnIndex+9).value = getTimeRemaining(issue)
        ws.cell(row=rowIndex, column=columnIndex+10).value = getTimeLogged(issue)
        ws.cell(row=rowIndex, column=columnIndex+11).value = getOriginalEstimate(issue)
        ws.cell(row=rowIndex, column=columnIndex+12).value = getStartFb(issue)
        ws.cell(row=rowIndex, column=columnIndex+13).value = getEndFb(issue)
        ws.cell(row=rowIndex, column=columnIndex+14).value = getFbCommitStatus(issue)
        ws.cell(row=rowIndex, column=columnIndex+15).value = getStretchReason(issue)
        ws.cell(row=rowIndex, column=columnIndex+16).value = getDelayReason(issue)
        ws.cell(row=rowIndex, column=columnIndex+17).value = getDelayExplanation(issue)
        ws.cell(row=rowIndex, column=columnIndex+18).value = getRfcFB(issue)
        ws.cell(row=rowIndex, column=columnIndex+19).value = getRfcStatus(issue)
        ws.cell(row=rowIndex, column=columnIndex+20).value = getCompetenceArea(issue)
        ws.cell(row=rowIndex, column=columnIndex+21).value = getFs0Ee(issue)
        ws.cell(row=rowIndex, column=columnIndex+22).value = getFs2Ee(issue)
        ws.cell(row=rowIndex, column=columnIndex+23).value = getFotLeader(issue)
        ws.cell(row=rowIndex, column=columnIndex+24).value = getWorkType(issue)
        ws.cell(row=rowIndex, column=columnIndex+25).value = getIssueType(issue)
        ws.cell(row=rowIndex, column=columnIndex+26).value = getFeatureId(issue)
        ws.cell(row=rowIndex, column=columnIndex+27).value = getReqArea(issue)
        ws.cell(row=rowIndex, column=columnIndex+28).value = getActType(issue)
        ws.cell(row=rowIndex, column=columnIndex+29).value = getText2(issue)

def genBacklogCAOutput(ws):
    print("Fetch backlogCA")
    backlog_CaHeaders = [
        ('Item ID', 'customfield_38702'),
        ('Key', 'issuekey'),
        ('Summary', 'summary'),
        ('Planned System Release', 'customfield_38724'),
        ('Business Priority 1', 'customfield_38716'),
        ('Assignee', 'assignee'),
        ('Status', 'status'),
        ('Team', 'customfield_29790'),
        ('Labels', 'labels'),
        ('ΣTime Remaining (h)', 'customfield_43291'),
        ('ΣLogged Effort (h)', 'customfield_43290'),
        ('ΣOriginal Estimate (h)', 'customfield_43292'),
        ('Start FB', 'customfield_38694'),
        ('End FB', 'customfield_38693'),
        ('FB Commit Status', 'customfield_38758'),
        ('Stretch Goal Reason', 'customfield_43893'),
        ('Delay Reason', 'customfield_38759'),
        ('Delay Explanation', 'customfield_38755'),
        ('Release committed FB', 'customfield_43490'),
        ('Release Committed Status', 'customfield_38728'),
        ('Competence Area', 'customfield_38690'),
        ('FS0 EE (h)', 'customfield_38737'),
        ('FS2 EE (h)', 'customfield_38692'),
        ('FOT Leader', 'customfield_38695'),
        ('Work Type', 'customfield_38368'),
        ('Issue Type', 'issuetype'),
        ('Feature ID', 'customfield_37381'),
        ('Requirement Area', 'customfield_38712'),
        ('Activity Type', 'customfield_38750'),
        ('Text 2', 'customfield_38727')
        ]
    columnIndex = 1
    for header in backlog_CaHeaders:
        ws.cell(row=1, column=columnIndex).value = header[0] + '(' + header[1] + ')'
        columnIndex += 1
    
    rowIndex = 2
    for issue in getIssues(backlogCAFilter, backlog_CaHeaders):
        exportCAIssueToExcel(ws, issue, rowIndex=rowIndex, columnIndex=1)
        rowIndex += 1
        # print(rowIndex)

def exportIssueToEpicExcel(ws, issue, rowIndex, columnIndex):
        # print(issue.key)
        ws.cell(row=rowIndex, column=columnIndex).value = getItemId(issue)
        ws.cell(row=rowIndex, column=columnIndex+1).value = getKey(issue)
        ws.cell(row=rowIndex, column=columnIndex+2).value = getSummary(issue)
        ws.cell(row=rowIndex, column=columnIndex+3).value = getAssignee(issue)
        ws.cell(row=rowIndex, column=columnIndex+4).value = getStartFb(issue)
        ws.cell(row=rowIndex, column=columnIndex+5).value = getEndFb(issue)
        ws.cell(row=rowIndex, column=columnIndex+6).value = getStatus(issue)
        ws.cell(row=rowIndex, column=columnIndex+7).value = getLabels(issue)
        ws.cell(row=rowIndex, column=columnIndex+8).value = getTimeRemaining(issue)
        ws.cell(row=rowIndex, column=columnIndex+9).value = getTimeLogged(issue)
        ws.cell(row=rowIndex, column=columnIndex+10).value = getOriginalEstimate(issue)
        ws.cell(row=rowIndex, column=columnIndex+11).value = getCompetenceArea(issue)
        ws.cell(row=rowIndex, column=columnIndex+12).value = getBusinessPrio1(issue)
        ws.cell(row=rowIndex, column=columnIndex+13).value = getTeam(issue)
        ws.cell(row=rowIndex, column=columnIndex+14).value = getFs0Ee(issue)
        ws.cell(row=rowIndex, column=columnIndex+15).value = getFs2Ee(issue)
        ws.cell(row=rowIndex, column=columnIndex+16).value = getPlannedSystemRelease(issue)
        ws.cell(row=rowIndex, column=columnIndex+17).value = getFotLeader(issue)
        ws.cell(row=rowIndex, column=columnIndex+18).value = getWorkType(issue)
        ws.cell(row=rowIndex, column=columnIndex+19).value = getIssueType(issue)
        ws.cell(row=rowIndex, column=columnIndex+20).value = getFeatureId(issue)

def genBacklogEpicOutput(ws):
    print("Fetch backlogEpic")
    backlog_EpicHeaders = [
        ('Item ID', 'customfield_38702'),
        ('Key', 'issuekey'),
        ('Summary', 'summary'),
        ('Assignee', 'assignee'),
        ('Start FB', 'customfield_38694'),
        ('End FB', 'customfield_38693'),
        ('Status', 'status'),
        ('Labels', 'labels'),
        ('ΣTime Remaining (h)', 'customfield_43291'),
        ('ΣLogged Effort (h)', 'customfield_43290'),
        ('ΣOriginal Estimate (h)', 'customfield_43292'),
        ('Competence Area', 'customfield_38690'),
        ('Business Priority 1', 'customfield_38716'),
        ('Team', 'customfield_29790'),
        ('FS0 EE (h)', 'customfield_38737'),
        ('FS2 EE (h)', 'customfield_38692'),
        ('Planned System Release', 'customfield_38724'),
        ('FOT Leader', 'customfield_38695'),
        ('Work Type', 'customfield_38368'),
        ('Issue Type', 'issuetype'),
        ('Feature ID', 'customfield_37381')
        ]
    columnIndex = 1
    for header in backlog_EpicHeaders:
        ws.cell(row=1, column=columnIndex).value = header[0] + '(' + header[1] + ')'
        columnIndex += 1
    
    rowIndex = 2
    for issue in getIssues(backlogEpicFilter, backlog_EpicHeaders):
        exportIssueToEpicExcel(ws, issue, rowIndex=rowIndex, columnIndex=1)
        rowIndex += 1
        # print(rowIndex)

def exportIssueToFS2EEExcel(ws, issue, rowIndex, columnIndex):
        # print(issue.key)
        ws.cell(row=rowIndex, column=columnIndex).value = getItemId(issue)
        ws.cell(row=rowIndex, column=columnIndex+1).value = getKey(issue)
        ws.cell(row=rowIndex, column=columnIndex+2).value = getSummary(issue)
        ws.cell(row=rowIndex, column=columnIndex+3).value = getAssignee(issue)
        ws.cell(row=rowIndex, column=columnIndex+4).value = getStartFb(issue)
        ws.cell(row=rowIndex, column=columnIndex+5).value = getEndFb(issue)
        ws.cell(row=rowIndex, column=columnIndex+6).value = getStatus(issue)
        ws.cell(row=rowIndex, column=columnIndex+7).value = getLabels(issue)
        ws.cell(row=rowIndex, column=columnIndex+8).value = getTimeRemaining(issue)
        ws.cell(row=rowIndex, column=columnIndex+9).value = getTimeLogged(issue)
        ws.cell(row=rowIndex, column=columnIndex+10).value = getOriginalEstimate(issue)
        ws.cell(row=rowIndex, column=columnIndex+11).value = getCompetenceArea(issue)
        ws.cell(row=rowIndex, column=columnIndex+12).value = getBusinessPrio1(issue)
        ws.cell(row=rowIndex, column=columnIndex+13).value = getTeam(issue)
        ws.cell(row=rowIndex, column=columnIndex+14).value = getFs0Ee(issue)
        ws.cell(row=rowIndex, column=columnIndex+15).value = getFs2Ee(issue)
        ws.cell(row=rowIndex, column=columnIndex+16).value = getPlannedSystemRelease(issue)
        ws.cell(row=rowIndex, column=columnIndex+17).value = getFotLeader(issue)
        ws.cell(row=rowIndex, column=columnIndex+18).value = getWorkType(issue)
        ws.cell(row=rowIndex, column=columnIndex+19).value = getIssueType(issue)
        ws.cell(row=rowIndex, column=columnIndex+20).value = getFeatureId(issue)
        ws.cell(row=rowIndex, column=columnIndex+21).value = getFS2Status(issue)

def genBacklogFS2EEOutput(ws):
    print("Fetch FS2EE")
    backlog_FS2EEHeaders = [
        ('Item ID', 'customfield_38702'),
        ('Key', 'issuekey'),
        ('Summary', 'summary'),
        ('Assignee', 'assignee'),
        ('Start FB', 'customfield_38694'),
        ('End FB', 'customfield_38693'),
        ('Status', 'status'),
        ('Labels', 'labels'),
        ('ΣTime Remaining (h)', 'customfield_43291'),
        ('ΣLogged Effort (h)', 'customfield_43290'),
        ('ΣOriginal Estimate (h)', 'customfield_43292'),
        ('Competence Area', 'customfield_38690'),
        ('Business Priority 1', 'customfield_38716'),
        ('Team', 'customfield_29790'),
        ('FS0 EE (h)', 'customfield_38737'),
        ('FS2 EE (h)', 'customfield_38692'),
        ('Planned System Release', 'customfield_38724'),
        ('FOT Leader', 'customfield_38695'),
        ('Work Type', 'customfield_38368'),
        ('Issue Type', 'issuetype'),
        ('Feature ID', 'customfield_37381'),
        ('FS Status', 'customfield_38705')
        ]
    columnIndex = 1
    for header in backlog_FS2EEHeaders:
        ws.cell(row=1, column=columnIndex).value = header[0] + '(' + header[1] + ')'
        columnIndex += 1
    
    rowIndex = 2
    for issue in getIssues(backlogFS2EeFilter, backlog_FS2EEHeaders):
        exportIssueToFS2EEExcel(ws, issue, rowIndex=rowIndex, columnIndex=1)
        rowIndex += 1
        # print(rowIndex)

def exportIssueToFFBExcel(ws, issue, rowIndex, columnIndex):
        # print(issue.key)
        ws.cell(row=rowIndex, column=columnIndex).value = getFeatureId(issue)
        ws.cell(row=rowIndex, column=columnIndex+1).value = getKey(issue)
        ws.cell(row=rowIndex, column=columnIndex+2).value = getSummary(issue)
        ws.cell(row=rowIndex, column=columnIndex+3).value = getStatus(issue)
        ws.cell(row=rowIndex, column=columnIndex+4).value = getBusinessPrio1(issue)
        ws.cell(row=rowIndex, column=columnIndex+5).value = getPlannedSystemRelease(issue)
        ws.cell(row=rowIndex, column=columnIndex+6).value = getSpecEndFB(issue)
        ws.cell(row=rowIndex, column=columnIndex+7).value = getFotLeader(issue)
        ws.cell(row=rowIndex, column=columnIndex+8).value = getIssueType(issue)
        ws.cell(row=rowIndex, column=columnIndex+9).value = getReqArea(issue)
        ws.cell(row=rowIndex, column=columnIndex+10).value = getCustomer(issue)
        ws.cell(row=rowIndex, column=columnIndex+11).value = getLabels(issue)
        ws.cell(row=rowIndex, column=columnIndex+12).value = getTargetReleases(issue)

def genBacklogFFBOutput(ws):
    print("Fetch FFB")
    backlog_FFBHeaders = [
        ('Feature ID', 'customfield_37381'),
        ('Key', 'issuekey'),
        ('Summary', 'summary'),
        ('Status', 'status'),
        ('Business Priority 1', 'customfield_38716'),
        ('Planned System Release', 'customfield_38724'),
        ('System Spec End FB', 'customfield_38739'),
        ('FOT Leader', 'customfield_38695'),
        ('Issue Type', 'issuetype'),
        ('Requirement Area', 'customfield_38712'),
        ('Customer', 'customfield_32006'),
        ('Labels', 'labels'),
        ('Target Releases', 'customfield_38710')
        ]
    columnIndex = 1
    for header in backlog_FFBHeaders:
        ws.cell(row=1, column=columnIndex).value = header[0] + '(' + header[1] + ')'
        columnIndex += 1
    
    rowIndex = 2
    for issue in getIssues(backlogFFBFilter, backlog_FFBHeaders):
        exportIssueToFFBExcel(ws, issue, rowIndex=rowIndex, columnIndex=1)
        rowIndex += 1
        # print(rowIndex)

def exportIssueToSubtaskExcel(ws, issue, rowIndex, columnIndex):
        # print(issue.key)
        ws.cell(row=rowIndex, column=columnIndex).value = getFeatureId(issue)
        ws.cell(row=rowIndex, column=columnIndex+1).value = getKey(issue)
        ws.cell(row=rowIndex, column=columnIndex+2).value = getSummary(issue)
        ws.cell(row=rowIndex, column=columnIndex+3).value = getAssignee(issue)
        ws.cell(row=rowIndex, column=columnIndex+4).value = getStatus(issue)
        ws.cell(row=rowIndex, column=columnIndex+5).value = getLabels(issue)
        ws.cell(row=rowIndex, column=columnIndex+6).value = getTimeRemaining(issue)
        ws.cell(row=rowIndex, column=columnIndex+7).value = getTimeLogged(issue)
        ws.cell(row=rowIndex, column=columnIndex+8).value = getOriginalEstimate(issue)
        ws.cell(row=rowIndex, column=columnIndex+9).value = getTeam(issue)
        ws.cell(row=rowIndex, column=columnIndex+10).value = getWorkType(issue)
        ws.cell(row=rowIndex, column=columnIndex+11).value = getIssueType(issue)
        ws.cell(row=rowIndex, column=columnIndex+12).value = getFeatureId(issue)
        ws.cell(row=rowIndex, column=columnIndex+13).value = getCreatedTime(issue)
        ws.cell(row=rowIndex, column=columnIndex+14).value = getUpdatedTime(issue)
        ws.cell(row=rowIndex, column=columnIndex+15).value = getResolvedTime(issue)

def genBacklogSubtaskOutput(ws):
    print("Fetch Subtask")
    backlog_SubtaskHeaders = [
        ('Item ID', 'customfield_38702'),
        ('Key', 'issuekey'),
        ('Summary', 'summary'),
        ('Assignee', 'assignee'),
        ('Status', 'status'),
        ('Labels', 'labels'),
        ('ΣTime Remaining (h)', 'customfield_43291'),
        ('ΣLogged Effort (h)', 'customfield_43290'),
        ('ΣOriginal Estimate (h)', 'customfield_43292'),
        ('Team', 'customfield_29790'),
        ('Work Type', 'customfield_38368'),
        ('Issue Type', 'issuetype'),
        ('Feature ID', 'customfield_37381'),
        ('Created', 'created'),
        ('Updated', 'updated'),
        ('Resolved', 'resolutiondate')
        ]
    columnIndex = 1
    for header in backlog_SubtaskHeaders:
        ws.cell(row=1, column=columnIndex).value = header[0] + '(' + header[1] + ')'
        columnIndex += 1
    
    rowIndex = 2
    for issue in getIssues(backlogSubtaskFilter, backlog_SubtaskHeaders):
        exportIssueToSubtaskExcel(ws, issue, rowIndex=rowIndex, columnIndex=1)
        rowIndex += 1
        # print(rowIndex)

t_backlogEpic = Thread(target=genBacklogEpicOutput, args=(ws_backlog_Epic,))
t_backlogFS2 = Thread(target=genBacklogFS2EEOutput, args=(ws_backlog_FS2EE,))
t_backlogFFB = Thread(target=genBacklogFFBOutput, args=(ws_backlog_FFB,))

t_backlogEpic.start()
t_backlogFS2.start()
t_backlogFFB.start()

genBacklogSubtaskOutput(ws_backlog_Subtask)
genBacklogCAOutput(ws_backlog_CA)

t_backlogEpic.join()
t_backlogFS2.join()
t_backlogFFB.join()

snapshotCollectionTime = strftime("%Y%m%d%H%M%S", localtime())
outputFile = 'Backlog_' + snapshotCollectionTime + '.xlsx'
wb.save(outputFile)
print("Elapsed Time: {}".format(datetime.now() - startTime))

print('collection done in {}'.format(outputFile))